﻿@page "/Form6"
@using System.Diagnostics.CodeAnalysis
@using BootstrapBlazor.OnlyServerZhongShun.Data.dbmodels
@using System.Data
@inject ILogger<Form6> NormalLogger
@inject AuthenticationStateProvider authenticationStateProvider
@inject ToastService ToastService
@inject IConfiguration _congifuration
@inject SqlSugarScope _SqlSugarScope
@attribute [Authorize(Roles = "超级管理员,业务总, 业务经理, 业务员")]
<HeadContent>
    <style>
        .multi-select {
            --bb-multi-select-min-height: 26px;
        }
    </style>
</HeadContent>

<div class="row g-1">
    <div class="col-5">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="查询日期" />
            <DateTimeRange @bind-Value="@NormalDateTimeRangeValue" OnConfirm="OnNormalConfirm" ShowSidebar="true" ViewMode="DatePickerViewMode.Date" IsEditable="true" DateFormat="yyyy-MM-dd">
                <TimePickerSetting ShowClockScale="true" IsAutoSwitch="false" />
            </DateTimeRange>
        </BootstrapInputGroup>
    </div>
    <div class="col-3">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="客户" />
            <Select IsVirtualize="true" Items="客户列表Items" @bind-Value="选中客户" OnSearchTextChanged="OnSearchTextChanged" ShowSearch="true" IsClearable="true"></Select>
        </BootstrapInputGroup>
    </div>
    <div class="col-4">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="部门" />
            <MultiSelect Items="@部门Items" @bind-Value="@选中部门List" OnSelectedItemsChanged="@部门选择改变事件" ShowToolbar="true" />
        </BootstrapInputGroup>
    </div>
</div>
    <div class="row g-1">
  @*   <div class="col-4">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="查询列" />
            <CheckboxList TValue="string" @bind-Value="@选中查询列" Items="@查询列" ShowLabel="false" ShowLabelTooltip="true"></CheckboxList>
        </BootstrapInputGroup>
    </div> *@
    <div class="col-3">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="养殖类别" />
            <Select IsVirtualize="true" Items="养殖类别Items" @bind-Value="选中养殖类别" ShowSearch="false" IsClearable="true"></Select>
        </BootstrapInputGroup>
    </div>
    <div class="col-3">
        <RadioList TValue="string" Items="@查询类别" @bind-Value="@选中查询类别" Class="custom-radio6" />
    </div>

    <div class="col-6">
        <Button OnClick="@Button_chaxun_Click" IsAsync="true" Color="Color.Primary">查询</Button>
        <Button OnClick="@Button_daochu" Color="Color.Info">导出</Button>
        </div>
</div>
@* <div class="table-fixed-header"> *@
<Table Class="form4table" TItem="DynamicObject" DynamicContext="DataTableDynamicContext"
           IsBordered="true" IsMultipleSelect="false" ShowToolbar="true" ShowRefresh="false" ShowDefaultButtons="false" IsPagination="true" ShowExportButton="false"
           OnExportAsync="OnExportAsync" OnSort="OnSort" ShowFooter="true" IsStriped="true" PageItemsSource="new int[]{1000,2000,3000,4000,5000,10000}" TableSize="TableSize.Compact" FixedMultipleColumn="true"
           Height="500" IsFixedHeader="true">
    <FooterTemplate>
        <tr>
            @if (DataTableDynamicContext != null && DataTableDynamicContext.DataTable.Rows.Count > 0)
            {
                @foreach (DataColumn property in DataTableDynamicContext.DataTable.Columns)
                {
                    <td>
                        @*  //如果不是 代理商名称,部门 列 *@
                        @* @if (property.ColumnName != "代理商名称" && property.ColumnName != "部门" && property.ColumnName != "销售员" && Type.GetTypeCode(property.DataType) == TypeCode.String) *@
                        @if (Type.GetTypeCode(property.DataType) != TypeCode.String && property.ColumnName != "退货率" && property.ColumnName != "往年退货率" && property.ColumnName != "序号")
                        {
                            //当前列的合计
                            var sum = DataTableDynamicContext.DataTable.AsEnumerable().Sum(dr =>
                            {
                                if (dr.IsNull(property.ColumnName)) return 0m;
                                object value = dr[property.ColumnName];
                                if (value is int)
                                    return (decimal)(int)value;
                                else if (value is decimal)
                                    return (decimal)value;
                                else
                                    return 0m;
                            });
                            @sum
                            }
                        else if (property.ColumnName == "序号")
                            {
                                @:合计
                        }
                        else if (property.ColumnName == "退货率")
                        {
                            switch (选中查询类别)
                            {
                                case "0":
                                    {
                                        if (DataTableDynamicContext.DataTable.Columns.Contains("本期净发货金额") == true && DataTableDynamicContext.DataTable.Columns.Contains("退货金额") == true)
                                        {


                                            var sum本期净发货金额 = DataTableDynamicContext.DataTable.AsEnumerable().Sum(dr =>
                                        {
                                                if (dr.IsNull("本期净发货金额")) return 0m;
                                                object value = dr["本期净发货金额"];
                                            if (value is int)
                                                return (decimal)(int)value;
                                            else if (value is decimal)
                                                return (decimal)value;
                                            else
                                                return 0m;
                                        });
                                        //销售差异
                                        var sum退货金额 = DataTableDynamicContext.DataTable.AsEnumerable().Sum(dr =>
                                        {
                                            if (dr.IsNull("退货金额")) return 0m;
                                            object value = dr["退货金额"];
                                            if (value is int)
                                                return (decimal)(int)value;
                                            else if (value is decimal)
                                                return (decimal)value;
                                            else
                                                return 0m;
                                        });

                                            if (sum本期净发货金额 != 0)
                                        {
                                                @((sum退货金额 / sum本期净发货金额).ToString("p2"))
                                        }
                                        }
                                        break;
                                    }
                                default:
                                    {
                                        if (DataTableDynamicContext.DataTable.Columns.Contains("本期净发货数") == true && DataTableDynamicContext.DataTable.Columns.Contains("退货数量") == true)
                                        {
                                           
                                        
                                        var sum本期净发货数 = DataTableDynamicContext.DataTable.AsEnumerable().Sum(dr =>
                                        {
                                                if (dr.IsNull("本期净发货数")) return 0m;
                                                object value = dr["本期净发货数"];
                                            if (value is int)
                                                return (decimal)(int)value;
                                            else if (value is decimal)
                                                return (decimal)value;
                                            else
                                                return 0m;
                                        });
                                        //销售差异
                                        var sum退货数量 = DataTableDynamicContext.DataTable.AsEnumerable().Sum(dr =>
                                        {
                                            if (dr.IsNull("退货数量")) return 0m;
                                            object value = dr["退货数量"];
                                            if (value is int)
                                                return (decimal)(int)value;
                                            else if (value is decimal)
                                                return (decimal)value;
                                            else
                                                return 0m;
                                        });

                                            if (sum本期净发货数 != 0)
                                        {
                                                @((sum退货数量 / sum本期净发货数).ToString("p2"))
                                        }
                                        }
                                        break;
                                    
                                    }
                            }
                        }
                        else
                        {
                            @:--
                        }

                    </td>
                }
            }

        </tr>
    </FooterTemplate>
</Table>
@* </div> *@

@code {
    private string username;

    [Inject]
    [NotNull]
    private ITableExport? TableExport { get; set; }
    [Inject]
    [NotNull]
    private ToastService? Toast { get; set; }

    private List<View_Custom> View_CustomList = new List<View_Custom>();
    private List<View_Depart> View_DepartList = new List<View_Depart>();

    private DateTimeRangeValue NormalDateTimeRangeValue { get; set; } = new();

    [NotNull]
    private IEnumerable<SelectedItem>? 查询列 { get; set; } = new List<SelectedItem>(new List<SelectedItem>
    {
        new SelectedItem { Text = "金额", Value = "金额" },
        new SelectedItem { Text = "数量", Value = "数量" }
    });
    [NotNull]
    private IEnumerable<SelectedItem>? 查询类别 { get; set; } = new List<SelectedItem>(new List<SelectedItem>
    {
        new SelectedItem { Text = "金额", Value = "0" },
        new SelectedItem { Text = "数量", Value = "1"}
    });

    private string 选中查询类别 { get; set; }
    private string 选中查询列 { get; set; }

    private List<View_Custom> view_Customs = new List<View_Custom>();
    private SelectedItem? 选中养殖类别 { get; set; }
    private IEnumerable<SelectedItem> 养殖类别Items => view_Customs.Select(i => new SelectedItem(i.养殖类别!, i.养殖类别!)).ToList();


    private IEnumerable<SelectedItem> 客户列表Items => View_CustomList.Select(i => new SelectedItem(i.cCusCode!, i.cCusName!)).ToList();
    private IEnumerable<SelectedItem> 部门Items => View_DepartList.Select(i => new SelectedItem(i.cDepCode!, i.cDepName!)).ToList();


    private SelectedItem? 选中客户 { get; set; }
    private IEnumerable<string> 选中部门List { get; set; }
    private string 选中部门InStr = "";

    [NotNull]
    private DataTableDynamicContext? DataTableDynamicContext { get; set; }

    protected override async void OnInitialized()
    {
        var authState = await authenticationStateProvider.GetAuthenticationStateAsync();
        var user = authState.User;
        username = user.Identity.Name;
        //今年1月1日
        NormalDateTimeRangeValue.Start = new DateTime(DateTime.Now.Year, 1, 1);
        //今天
        NormalDateTimeRangeValue.End = DateTime.Now.AddDays(-1);

        View_CustomList = _SqlSugarScope.Queryable<View_Custom>().ToList();
        //加一个请选择
        View_CustomList.Insert(0, new View_Custom { cCusCode = "", cCusName = "请选择" });
        List<View_Custom> cus = _SqlSugarScope.Queryable<View_Custom>().Where(it => !string.IsNullOrEmpty(it.养殖类别)).Distinct().Select(it => new View_Custom { 养殖类别 = it.养殖类别 }).ToList();
        view_Customs = cus;
        view_Customs.Insert(0, new View_Custom { 养殖类别 = "请选择" });
        view_Customs.Add(new View_Custom { 养殖类别 = "非小棚虾" });
        //读取配置文件
        UserDataAuth userDataAuth = _SqlSugarScope.Queryable<UserDataAuth>().Where(it => it.UserName == username).First();
        List<Guid> guids = userDataAuth.CanViewDepart.Split(",").ToList().Select(it => Guid.Parse(it)).ToList();
        List<Sys_Department> depts = _SqlSugarScope.Queryable<Sys_Department>().Where(it => guids.Contains(it.DepartmentId)).ToList();
        List<string> deptNames = depts.Select(it => it.DepartmentName).ToList();
        var PaiChuBuMen = _congifuration.GetSection("PaiChuBuMen").Value;
        List<View_Depart> deptss = _SqlSugarScope.Queryable<View_Depart>().Where($"cDepCode not in ({PaiChuBuMen})").ToList();
        View_DepartList = deptss.Where(it => deptNames.Contains(it.cDepName)).ToList();
        // //加一个请选择
        // View_DepartList.Insert(0, new View_Depart { cDepCode = "", cDepName = "请选择" });

    }
    private Task OnNormalConfirm(DateTimeRangeValue value)
    {
        NormalLogger.LogDebug($"选择的时间范围是: {value.Start:yyyy-MM-dd HH:mm:ss} - {value.End:yyyy-MM-dd HH:mm:ss}");
        //遍历输出选中的客户
        if (选中客户 != null)
        {
            NormalLogger.LogDebug($"选中的客户是: {选中客户.Text}");
        }
        //遍历输出选中的部门
        if (选中部门List != null)
        {
            foreach (var item in 选中部门List)
            {

            }
        }
        return Task.CompletedTask;
    }
    private IEnumerable<SelectedItem> OnSearchTextChanged(string searchText)
    {
        return View_CustomList.Where(i => i.cCusName!.Contains(searchText, StringComparison.OrdinalIgnoreCase)).Select(i => new SelectedItem(i.cCusCode!, i.cCusName!));
    }

    private Task 部门选择改变事件(IEnumerable<SelectedItem> items)
    {
        选中部门InStr = string.Join("','", items.Select(it => it.Text).ToArray());
        return Task.CompletedTask;
    }

    private async Task<bool> OnExportAsync(ITableExportDataContext<DynamicObject> context)
    {
        // 导出符合条件的所有数据 data
        var ret = await TableExport.ExportExcelAsync(DataTableDynamicContext.GetItems(), context.Columns, $"Test_{DateTime.Now:yyyyMMddHHmmss}.xlsx");

        // 返回 true 时自动弹出提示框
        // await ShowToast(ret);
        return ret;
    }
    private async void Button_daochu(MouseEventArgs e)
    {
        //复制一个table用来导出
        DataTable dt = DataTableDynamicContext.DataTable.Copy();
        //增加一行合计
        DataRow dr = dt.NewRow();


        if (选中查询类别 == "0")
        {
            dr["净发货差异"] = dt.AsEnumerable().Sum(dr =>
                                {
                                    if (dr.IsNull("净发货差异")) return 0m;
                                    object value = dr["净发货差异"];
                                    if (value is int)
                                        return (decimal)(int)value;
                                    else if (value is decimal)
                                        return (decimal)value;
                                    else
                                        return 0m;
                                });
            dr["退货差异"] = dt.AsEnumerable().Sum(dr =>
            {
                if (dr.IsNull("退货差异")) return 0m;
                object value = dr["退货差异"];
                if (value is int)
                    return (decimal)(int)value;
                else if (value is decimal)
                    return (decimal)value;
                else
                    return 0m;
            });
            dr["发货金额"] = dt.AsEnumerable().Sum(dr =>
            {
                if (dr.IsNull("发货金额")) return 0m;
                object value = dr["发货金额"];
                if (value is int)
                    return (decimal)(int)value;
                else if (value is decimal)
                    return (decimal)value;
                else
                    return 0m;
            });
            dr["质量退货金额"] = dt.AsEnumerable().Sum(dr =>
            {
                if (dr.IsNull("质量退货金额")) return 0m;
                object value = dr["质量退货金额"];
                if (value is int)
                    return (decimal)(int)value;
                else if (value is decimal)
                    return (decimal)value;
                else
                    return 0m;
            });
            dr["调入金额"] = dt.AsEnumerable().Sum(dr =>
            {
                if (dr.IsNull("调入金额")) return 0m;
                object value = dr["调入金额"];
                if (value is int)
                    return (decimal)(int)value;
                else if (value is decimal)
                    return (decimal)value;
                else
                    return 0m;
            });
            dr["调出金额"] = dt.AsEnumerable().Sum(dr =>
            {
                if (dr.IsNull("调出金额")) return 0m;
                object value = dr["调出金额"];
                if (value is int)
                    return (decimal)(int)value;
                else if (value is decimal)
                    return (decimal)value;
                else
                    return 0m;
            });
            dr["退货金额"] = dt.AsEnumerable().Sum(dr =>
            {
                if (dr.IsNull("退货金额")) return 0m;
                object value = dr["退货金额"];
                if (value is int)
                    return (decimal)(int)value;
                else if (value is decimal)
                    return (decimal)value;
                else
                    return 0m;
            });
            dr["本期净发货金额"] = dt.AsEnumerable().Sum(dr =>
            {
                if (dr.IsNull("本期净发货金额")) return 0m;
                object value = dr["本期净发货金额"];
                if (value is int)
                    return (decimal)(int)value;
                else if (value is decimal)
                    return (decimal)value;
                else
                    return 0m;
            });

            dr["同期净发货金额"] = dt.AsEnumerable().Sum(dr =>
            {
                if (dr.IsNull("同期净发货金额")) return 0m;
                object value = dr["同期净发货金额"];
                if (value is int)
                    return (decimal)(int)value;
                else if (value is decimal)
                    return (decimal)value;
                else
                    return 0m;
            });
            dr["退货率"] = (decimal)dr["发货金额"] == 0 ? 0 : (decimal)dr["退货金额"] / (decimal)dr["发货金额"];
        }else
        {
            dr["净发货差异"] = dt.AsEnumerable().Sum(dr =>
            {
                if (dr.IsNull("净发货差异")) return 0m;
                object value = dr["净发货差异"];
                if (value is int)
                    return (decimal)(int)value;
                else if (value is decimal)
                    return (decimal)value;
                else
                    return 0m;
            });
            dr["退货差异"] = dt.AsEnumerable().Sum(dr =>
            {
                if (dr.IsNull("退货差异")) return 0m;
                object value = dr["退货差异"];
                if (value is int)
                    return (decimal)(int)value;
                else if (value is decimal)
                    return (decimal)value;
                else
                    return 0m;
            });
            dr["发货数量"] = dt.AsEnumerable().Sum(dr =>
            {
                if (dr.IsNull("发货数量")) return 0m;
                object value = dr["发��数量"];
                if (value is int)
                    return (decimal)(int)value;
                else if (value is decimal)
                    return (decimal)value;
                else
                    return 0m;
            });
            dr["质量退货数量"] = dt.AsEnumerable().Sum(dr =>
            {
                if (dr.IsNull("质量退货数量")) return 0m;
                object value = dr["质量退货数量"];
                if (value is int)
                    return (decimal)(int)value;
                else if (value is decimal)
                    return (decimal)value;
                else
                    return 0m;
            });
            dr["调入数量"] = dt.AsEnumerable().Sum(dr =>
            {
                if (dr.IsNull("调入数量")) return 0m;
                object value = dr["调��数量"];
                if (value is int)
                    return (decimal)(int)value;
                else if (value is decimal)
                    return (decimal)value;
                else
                    return 0m;
            });
            dr["调出数量"] = dt.AsEnumerable().Sum(dr =>
            {
                if (dr.IsNull("调出数量")) return 0m;
                object value = dr["调��数量"];
                if (value is int)
                    return (decimal)(int)value;
                else if (value is decimal)
                    return (decimal)value;
                else
                    return 0m;
            });
            dr["退货数量"] = dt.AsEnumerable().Sum(dr =>
            {
                if (dr.IsNull("退货数量")) return 0m;
                object value = dr["退��数量"];
                if (value is int)
                    return (decimal)(int)value;
                else if (value is decimal)
                    return (decimal)value;
                else
                    return 0m;
            });
            dr["本期净发货数"] = dt.AsEnumerable().Sum(dr =>
            {
                if (dr.IsNull("本期净发货数")) return 0m;
                object value = dr["本期净发货数"];
                if (value is int)
                    return (decimal)(int)value;
                else if (value is decimal)
                    return (decimal)value;
                else
                    return 0m;
            });
            dr["同期净发货数"] = dt.AsEnumerable().Sum(dr =>
            {
                if (dr.IsNull("同期净发货数")) return 0m;
                object value = dr["同期净发货数"];
                if (value is int)
                    return (decimal)(int)value;
                else if (value is decimal)
                    return (decimal)value;
                else
                    return 0m;
            });
            dr["退货率"] = (decimal)dr["发货数量"] == 0 ? 0 : (decimal)dr["退货数量"] / (decimal)dr["发货数量"];

        }
         dt.Rows.Add(dr);
        DataTableDynamicContext dataTable = new DataTableDynamicContext(dt);
        var ret = await TableExport.ExportExcelAsync(dataTable.GetItems(), DataTableDynamicContext.GetColumns(), $"发货状况分析表_{DateTime.Now:yyyyMMddHHmmss}.xlsx");

        // 导出符合条件的所有数据 data
        // var ret = await TableExport.ExportExcelAsync(DataTableDynamicContext.GetItems(), DataTableDynamicContext.GetColumns(), $"发货状况分析_{DateTime.Now:yyyyMMddHHmmss}.xlsx");
        ToastService.Success("提示", "导出成功");
    }

    private string OnSort(string sortName, SortOrder sortOrder)
    {

        string sortString = "";
        if (sortOrder == SortOrder.Asc)
        {
            sortString = $"{sortName} asc";
        }
        else if (sortOrder == SortOrder.Desc)
        {
            sortString = $"{sortName} desc";
        }
        else
        {
            sortString = $"{sortName}";
        }
        DataTable sortTable = DataTableDynamicContext.DataTable.Clone();

        DataView dv = DataTableDynamicContext.DataTable.DefaultView;
        dv.Sort = sortString;
        sortTable = dv.ToTable();
        显示数据(sortTable);
        return sortString;
    }
    private decimal sum1;
    private decimal sum2;
    private decimal sum3;
    private decimal sum4;
    private decimal sum5;
    private decimal sum6;
    private decimal sum7;
    private decimal sum8;

    private async void Button_chaxun_Click(MouseEventArgs e)
    {
        sum1 = 0;
        sum2 = 0;
        sum3 = 0;
        sum4 = 0;
        sum5 = 0;
        sum6 = 0;
        sum7 = 0;
        sum8 = 0;
        //客户发货排名
        string sql;
        string 年度 = this.NormalDateTimeRangeValue.End.Year.ToString();
        string 今年开始日期 = this.NormalDateTimeRangeValue.Start.ToString("yyyy-MM-dd");
        string 今年结束日期 = this.NormalDateTimeRangeValue.End.ToString("yyyy-MM-dd");
        string 去年开始日期 = this.NormalDateTimeRangeValue.Start.AddYears(-1).ToString("yyyy-MM-dd");
        string 去年结束日期 = this.NormalDateTimeRangeValue.End.AddYears(-1).ToString("yyyy-MM-dd");
        string 去年 = 去年开始日期.Substring(0, 4);
        string 今年sql;
        string 去年sql;

        // string loginuser = username;
        string loginuser = "admin";
        sql = "SELECT DepartmentName FROM [netcoredev].[dbo].[View_PersonDepart] where  cPersonName='" + loginuser + "'";
        string depart = _SqlSugarScope.Ado.GetString(sql);

        string guid = Guid.NewGuid().ToString();  ///本次查询统一的
        sql = "select docyear, dbname FROM      Tset where docyear='" + 年度 + "'";
        if (string.IsNullOrEmpty(选中部门InStr) == true)
        {
            选中部门InStr += string.Join("','", View_DepartList.Select(it => it.cDepName));
        }
        string 部门清单 = "('" + 选中部门InStr + "')";
        DataTable tb1 = _SqlSugarScope.Ado.GetDataTable(sql);
        if (tb1.Rows.Count > 0)
        {
            今年sql = tb1.Rows[0]["dbname"].ToString();
            sql = "delete T客户发货 where usercode='" + loginuser + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);

            //1,发货数量, 发货金额
            #region
            sql = @"insert into   T发货状况分析 (guid, usercode, cInvCode, dDate,cwhcode,cCusCode,cPersonCode, 发货数量, 发货金额)";
            sql += "select '" + guid + "','" + loginuser + "',cInvCode ,dDate,cwhcode,cCusCode,cPersonCode,iQuantity,iMoney";
            sql += " from View_发票明细 where 1=1 ";
            if (loginuser != "admin")
            {
                sql += " and cCusCode in (SELECT  cCusCode FROM View_CustomDepart where  cDepName='" + depart + "')";
                sql += " and cCusCode in (SELECT  cCusCode FROM TPersonCustom where cPersonName='" + loginuser + "')";
            }

            sql += " and dDate>='" + 今年开始日期 + "' and dDate<='" + 今年结束日期 + "'";
            // sql += " and iQuantity>0";
            sql += " and cwhcode in ('03','14')";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            #endregion
            //2,质量退货
            #region
            sql = @"insert into   T发货状况分析 (guid, usercode, cInvCode, dDate,cwhcode,cCusCode,cPersonCode, 质量退货数量, 质量退货金额)";
            sql += "select '" + guid + "','" + loginuser + "',cInvCode ,dDate,cwhcode,cCusCode,cPersonCode,iQuantity,iMoney";
            sql += " from View_发票明细 where 1=1 ";
            if (loginuser != "admin")
            {
                sql += " and cCusCode in (SELECT  cCusCode FROM View_CustomDepart where  cDepName='" + depart + "')";
                sql += " and cCusCode in (SELECT  cCusCode FROM TPersonCustom where cPersonName='" + loginuser + "')";
            }
            sql += " and dDate>='" + 今年开始日期 + "' and dDate<='" + 今年结束日期 + "'";
            sql += "and cwhcode='017'";//待处理库负数
            // sql += " and iQuantity<0";//待处理库负数

            _SqlSugarScope.Ado.ExecuteCommand(sql);
            #endregion
            //3,调入数量, 调入金额
            #region
            sql = @"insert into   T发货状况分析 (guid, usercode, cInvCode, dDate,cwhcode,cCusCode,cPersonCode,  调入数量, 调入金额)";
            sql += "select '" + guid + "','" + loginuser + "',cInvCode ,dDate,cwhcode,cCusCode,cPersonCode,iQuantity,iMoney";
            sql += " from View_发票明细 where 1=1 ";
            if (loginuser != "admin")
            {
                sql += " and cCusCode in (SELECT  cCusCode FROM View_CustomDepart where  cDepName='" + depart + "')";
                sql += " and cCusCode in (SELECT  cCusCode FROM TPersonCustom where cPersonName='" + loginuser + "')";
            }
            sql += " and dDate>='" + 今年开始日期 + "' and dDate<='" + 今年结束日期 + "'";
            sql += "and cwhcode='10' and iQuantity>0";//调入

            _SqlSugarScope.Ado.ExecuteCommand(sql);
            #endregion
            //4,调出数量, 调出金额
            #region
            sql = @"insert into   T发货状况分析 (guid, usercode, cInvCode, dDate,cwhcode,cCusCode,cPersonCode, 调出数量, 调出金额)";
            sql += "select '" + guid + "','" + loginuser + "',cInvCode ,dDate,cwhcode,cCusCode,cPersonCode,iQuantity,iMoney";
            sql += " from View_发票明细 where 1=1 ";
            if (loginuser != "admin")
            {
                sql += " and cCusCode in (SELECT  cCusCode FROM View_CustomDepart where  cDepName='" + depart + "')";
                sql += " and cCusCode in (SELECT  cCusCode FROM TPersonCustom where cPersonName='" + loginuser + "')";
            }
            sql += " and dDate>='" + 今年开始日期 + "' and dDate<='" + 今年结束日期 + "'";
            sql += "and cwhcode='10' and iQuantity<0";//调出

            _SqlSugarScope.Ado.ExecuteCommand(sql);
            #endregion
            //5,退货数量, 退货金额
            #region
            sql = @"insert into   T发货状况分析 (guid, usercode, cInvCode, dDate,cwhcode,cCusCode,cPersonCode, 退货数量, 退货金额)";
            sql += "select '" + guid + "','" + loginuser + "',cInvCode ,dDate,cwhcode,cCusCode,cPersonCode,iQuantity,iMoney";
            sql += " from View_发票明细 where 1=1 ";
            if (loginuser != "admin")
            {
                sql += " and cCusCode in (SELECT  cCusCode FROM View_CustomDepart where  cDepName='" + depart + "')";
                sql += " and cCusCode in (SELECT  cCusCode FROM TPersonCustom where cPersonName='" + loginuser + "')";
            }
            sql += " and dDate>='" + 今年开始日期 + "' and dDate<='" + 今年结束日期 + "'";
            sql += "and cwhcode='06' and iQuantity<0";//退货库负数
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            #endregion

            //1,发货数量, 发货金额
            #region
            sql = @"insert into   T发货状况分析 (guid, usercode, cInvCode, dDate,cwhcode,cCusCode,cPersonCode, 往年发货数量, 往年发货金额)";
            sql += "select '" + guid + "','" + loginuser + "',cInvCode ,dDate,cwhcode,cCusCode,cPersonCode,iQuantity,iMoney";
            sql += " from View_发票明细 where 1=1 ";
            if (loginuser != "admin")
            {
                sql += " and cCusCode in (SELECT  cCusCode FROM View_CustomDepart where  cDepName='" + depart + "')";
                sql += " and cCusCode in (SELECT  cCusCode FROM TPersonCustom where cPersonName='" + loginuser + "')";
            }

            sql += " and dDate>='" + 去年开始日期 + "' and dDate<='" + 去年结束日期 + "'";
            sql += " and cwhcode in ('03','14')";
            // sql += " and iQuantity>0";

            _SqlSugarScope.Ado.ExecuteCommand(sql);
            #endregion
            //2,质量退货
            #region
            sql = @"insert into   T发货状况分析 (guid, usercode, cInvCode, dDate,cwhcode,cCusCode,cPersonCode, 往年质量退货数量, 往年质量退货金额)";
            sql += "select '" + guid + "','" + loginuser + "',cInvCode ,dDate,cwhcode,cCusCode,cPersonCode,iQuantity,iMoney";
            sql += " from View_发票明细 where 1=1 ";
            if (loginuser != "admin")
            {
                sql += " and cCusCode in (SELECT  cCusCode FROM View_CustomDepart where  cDepName='" + depart + "')";
                sql += " and cCusCode in (SELECT  cCusCode FROM TPersonCustom where cPersonName='" + loginuser + "')";
            }
            sql += " and dDate>='" + 去年开始日期 + "' and dDate<='" + 去年结束日期 + "'";
            sql += "and cwhcode='017'";//待处理库负数
            // sql += " and iQuantity<0";//待处理库负数

            _SqlSugarScope.Ado.ExecuteCommand(sql);
            #endregion

            //3,调入数量, 调入金额
            #region
            sql = @"insert into   T发货状况分析 (guid, usercode, cInvCode, dDate,cwhcode,cCusCode,cPersonCode,  往年调入数量, 往年调入金额)";
            sql += "select '" + guid + "','" + loginuser + "',cInvCode ,dDate,cwhcode,cCusCode,cPersonCode,iQuantity,iMoney";
            sql += " from View_发票明细 where 1=1 ";
            if (loginuser != "admin")
            {
                sql += " and cCusCode in (SELECT  cCusCode FROM View_CustomDepart where  cDepName='" + depart + "')";
                sql += " and cCusCode in (SELECT  cCusCode FROM TPersonCustom where cPersonName='" + loginuser + "')";
            }
            sql += " and dDate>='" + 去年开始日期 + "' and dDate<='" + 去年结束日期 + "'";
            sql += "and cwhcode='10' and iQuantity>0";//调入

            _SqlSugarScope.Ado.ExecuteCommand(sql);
            #endregion
            //4,调出数量, 调出金额
            #region
            sql = @"insert into   T发货状况分析 (guid, usercode, cInvCode, dDate,cwhcode,cCusCode,cPersonCode, 往年调出数量, 往年调出金额)";
            sql += "select '" + guid + "','" + loginuser + "',cInvCode ,dDate,cwhcode,cCusCode,cPersonCode,iQuantity,iMoney";
            sql += " from View_发票明细 where 1=1 ";
            if (loginuser != "admin")
            {
                sql += " and cCusCode in (SELECT  cCusCode FROM View_CustomDepart where  cDepName='" + depart + "')";
                sql += " and cCusCode in (SELECT  cCusCode FROM TPersonCustom where cPersonName='" + loginuser + "')";
            }
            sql += " and dDate>='" + 去年开始日期 + "' and dDate<='" + 去年结束日期 + "'";
            sql += "and cwhcode='10' and iQuantity<0";//调出

            _SqlSugarScope.Ado.ExecuteCommand(sql);
            #endregion

            //5,退货数量, 退货金额
            #region
            sql = @"insert into   T发货状况分析 (guid, usercode, cInvCode, dDate,cwhcode,cCusCode,cPersonCode, 往年退货数量, 往年退货金额)";
            sql += "select '" + guid + "','" + loginuser + "',cInvCode ,dDate,cwhcode,cCusCode,cPersonCode,iQuantity,iMoney";
            sql += " from View_发票明细 where 1=1 ";
            if (loginuser != "admin")
            {
                sql += " and cCusCode in (SELECT  cCusCode FROM View_CustomDepart where  cDepName='" + depart + "')";
                sql += " and cCusCode in (SELECT  cCusCode FROM TPersonCustom where cPersonName='" + loginuser + "')";
            }
            sql += " and dDate>='" + 去年开始日期 + "' and dDate<='" + 去年结束日期 + "'";
            sql += "and cwhcode='06' and iQuantity<0";//退货库负数
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            #endregion

            //更新客户
            sql = "update  a set a.代理商名称=b.cCusName ,a.销售员=b.cPersonName,a.cPersonCode=b.cCusPPerson ";
            sql += " from T发货状况分析 a, View_CustomPerson b where a.cCusCode=b.cCusCode and a.guid='" + guid + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            ////更新销售员
            // sql = "update  a set a.销售员=b.cPersonName ";
            // sql += " from T发货状况分析 a, View_Person b where a.cPersonCode=b.cPersonCode and a.guid='" + guid + "'";
            // _SqlSugarScope.Ado.ExecuteCommand(sql);
            //更新部门
            sql = "update  a set a.部门=b.DepartmentName,a.Depart=b.[cDepCode] ";
            sql += " from T发货状况分析 a, View_PersonDepart b where a.cPersonCode=b.cPersonCode and a.guid='" + guid + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            //更新事业部（就是一级部门）
            sql = "update  a set a.事业部=b.事业部 ";
            sql += " from T发货状况分析 a, T事业部 b where  substring(a.[Depart] ,0,4)=b.编号 and a.guid='" + guid + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            //更新产品
            sql = "update  a set a.产品名称=b.cInvName,a.规格=b.cInvStd,a.类别=b.类别名称 ";
            sql += " from T发货状况分析 a, View_Inventory b where a.cInvCode=b.cInvCode and a.guid='" + guid + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);

            //养殖类别
            sql = "update  a set a.养殖类别=b.养殖类别 ";
            sql += " from T发货状况分析 a, View_Custom b where  a.cCusCode=b.cCusCode and a.guid='" + guid + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);

            switch (选中查询类别)
            {
                case "0":
                    { 
                        sql = @" select    产品名称, 规格, 类别,
                    Convert(decimal(18,4),(((sum(isnull(发货金额,0))+sum(isnull(退货金额,0))+sum(isnull(质量退货金额,0))+sum(isnull(调入金额,0))+sum(isnull(调出金额,0)))-(sum(isnull(往年发货金额,0))+sum(isnull(往年退货金额,0))+sum(isnull(往年质量退货金额,0))+sum(isnull(往年调入金额,0))+sum(isnull(往年调出金额,0)))))/10000) 净发货差异,
                    Convert(decimal(18,4),(sum(isnull(退货金额,0)) - sum(isnull(往年退货金额,0)))/10000)  退货差异,
                    Convert(decimal(18,4),sum(isnull(发货金额,0))/10000) 发货金额,
                   Convert(decimal(18,4),sum(isnull(质量退货金额,0))/10000) 质量退货金额,
                    Convert(decimal(18,4),sum(isnull(调入金额,0))/10000) 调入金额,
                     Convert(decimal(18,4),sum(isnull(调出金额,0))/10000) 调出金额,
                     Convert(decimal(18,4),sum(isnull(退货金额,0))/10000) 退货金额,
                    Convert(decimal(18,4),(sum(isnull(发货金额,0))+sum(isnull(退货金额,0))+sum(isnull(质量退货金额,0))+sum(isnull(调入金额,0))+sum(isnull(调出金额,0)))/10000) 本期净发货金额,
                    Convert(decimal(18,4),(sum(isnull(往年发货金额,0))+sum(isnull(往年退货金额,0))+sum(isnull(往年质量退货金额,0))+sum(isnull(往年调入金额,0))+sum(isnull(往年调出金额,0)))/10000) 同期净发货金额,
                     CASE  WHEN sum(isnull(发货金额,0))+sum(isnull(退货金额,0)) > 0 THEN (sum(isnull(退货金额,0)))/(sum(isnull(发货金额,0))+sum(isnull(退货金额,0))) ELSE 0 END as 退货率
                    --sum(isnull(往年发货金额,0)) 往年发货金额,
                   -- sum(isnull(往年质量退货金额,0)) 往年质量退货金额,
                    --sum(isnull(往年调入金额,0)) 往年调入金额,
                    -- sum(isnull(往年调出金额,0)) 往年调出金额,
                     --sum(isnull(往年退货金额,0)) 往年退货金额,
                    --sum(isnull(往年发货金额,0))+sum(isnull(往年退货金额,0)) 往年净发货金额, 0.0 往年退货率

                    FROM T发货状况分析 where guid='" + guid + "' ";
                        break; 
                    }
                default:
                    { 
                        sql = @" select    产品名称, 规格, 类别,
                    (sum(isnull(发货数量,0))+sum(isnull(退货数量,0))+sum(isnull(质量退货数量,0))+sum(isnull(调入数量,0))+sum(isnull(调出数量,0)))-(sum(isnull(往年发货数量,0))+sum(isnull(往年退货数量,0))+sum(isnull(往年质量退货数量,0))+sum(isnull(往年调入数量,0))+sum(isnull(往年调出数量,0))) 净发货差异,
                    sum(isnull(退货数量,0))-sum(isnull(往年退货数量,0)) 退货差异,
                    sum(isnull( 发货数量,0)) 发货数量 , 
                    sum(isnull(质量退货数量,0)) 质量退货数量,
                    sum(isnull(调入数量,0)) 调入数量,
                    sum(isnull(调出数量,0)) 调出数量,
                    sum(isnull(退货数量,0)) 退货数量,
                    sum(isnull(发货数量,0))+sum(isnull(退货数量,0))+sum(isnull(质量退货数量,0))+sum(isnull(调入数量,0))+sum(isnull(调出数量,0)) 本期净发货数,
                    sum(isnull(往年发货数量,0))+sum(isnull(往年退货数量,0))+sum(isnull(往年质量退货数量,0))+sum(isnull(往年调入数量,0))+sum(isnull(往年调出数量,0)) 同期净发货数,
                    CASE  WHEN sum(isnull(发货数量,0))+sum(isnull(退货数量,0)) > 0 THEN (sum(isnull(退货数量,0)))/(sum(isnull(发货数量,0))+sum(isnull(退货数量,0))) ELSE 0 END as 退货率

                    --sum(isnull(发货金额,0)) 发货金额,
                    --sum(isnull(质量退货金额,0))质量退货金额,
                    --sum(isnull(调入金额,0)) 调入金额,
                     --sum(isnull(调出金额,0)) 调出金额,
                    -- sum(isnull(退货金额,0)) 退货金额,
                    --sum(isnull(发货金额,0))+sum(isnull(退货金额,0)) 净发货金额,  0.0 退货率,
                    --sum(isnull(往年发货数量,0)) 往年发货数量 , sum(isnull(往年发货金额,0)) 往年发货金额,
                    --sum(isnull(往年质量退货数量,0)) 往年质量退货数量,sum(isnull(往年质量退货金额,0)) 往年质量退货金额,
                    --sum(isnull(往年调入数量,0)) 往年调入数量, sum(isnull(往年调入金额,0)) 往年调入金额,
                    --sum(isnull(往年调出数量,0)) 往年调出数量, sum(isnull(往年调出金额,0)) 往年调出金额,
                    --sum(isnull(往年退货数量,0)) 往年退货数量, sum(isnull(往年退货金额,0)) 往年退货金额,
                    --sum(isnull(往年发货数量,0))+sum(isnull(往年退货数量,0)) 往年净发货数,
                    --sum(isnull(往年发货金额,0))+sum(isnull(往年退货金额,0)) 往年净发货金额, 0.0 往年退货率

                    FROM T发货状况分析 where guid='" + guid + "' ";
                        break; 
                    }

            }
            //显示数据
            if (this.选中养殖类别?.Text != "请选择" && this.选中养殖类别?.Text != "非小棚虾")
            {
                sql += " and 养殖类别='" + this.选中养殖类别.Text + "'";
            }
            if (this.选中养殖类别?.Text != "请选择" && this.选中养殖类别?.Text == "非小棚虾")
            {
                sql += " and 养殖类别 is null";
            }
            if (string.IsNullOrEmpty(this.选中客户?.Text) == false && this.选中客户?.Text != "请选择")
            {
                sql += " and 代理商名称 like '%" + this.选中客户.Text + "%'";
            }
            sql += " and 部门 in " + 部门清单;
            sql += " group by cInvCode, 产品名称, 规格, 类别";
            DataTable dataTableHZ = _SqlSugarScope.Ado.GetDataTable(sql);
            
            sql = " delete T发货状况分析 where  guid='" + guid + "' ";

            _SqlSugarScope.Ado.ExecuteCommand(sql);
            显示数据(dataTableHZ);
            await ToastService.Information("提示", "查询成功");
            // if (选中查询列 != null)
            // {

            //     if (选中查询列.Split(",").Contains("金额") == true && 选中查询列.Split(",").Contains("数量") == false)
            //     {
            //         // if (dataTableHZ != null && dataTableHZ.Columns.Contains("本年次数"))
            //         // {
            //         //     dataTableHZ.Columns.Remove("本年次数");
            //         // }
            //         //移除包含数量的列
            //         for (int i = dataTableHZ.Columns.Count - 1; i >= 0; i--)
            //         {
            //             if (dataTableHZ.Columns[i].ColumnName.Contains("数量"))
            //             {
            //                 dataTableHZ.Columns.RemoveAt(i);
            //             }
            //         }

            //     }
            //     if (选中查询列.Split(",").Contains("金额") == false && 选中查询列.Split(",").Contains("数量") == true)
            //     {
            //         // if (dataTableHZ != null && dataTableHZ.Columns.Contains("发货金额"))
            //         // {
            //         //     dataTableHZ.Columns.Remove("发货金额");
            //         // }

            //         //移除包含金额的列
            //         for (int i = dataTableHZ.Columns.Count - 1; i >= 0; i--)
            //         {
            //             if (dataTableHZ.Columns[i].ColumnName.Contains("金额"))
            //             {
            //                 dataTableHZ.Columns.RemoveAt(i);
            //             }
            //         }
            //     }
            // }
            
        }
        else
        {
            await ToastService.Success("提示", "选择的日期有误", autoHide: true);
        }
    }

    public void 显示数据(DataTable dataTableHZ)
    {


        if (dataTableHZ != null && dataTableHZ.Rows.Count > 0)
        {
                for (int i = 0; i < dataTableHZ.Rows.Count; i++)
                {
                    DataRow dr = dataTableHZ.Rows[i];
                    decimal 退货率 = 0;
                    decimal 往年退货率 = 0;
                    // if (decimal.TryParse(dr["净发货金额"].ToString(), out decimal result) == true && result != 0)
                    // {
                    //     // decimal a = Math.Abs(Pub.ToDecimal(dr["退货金额"].ToString()));
                    //     // decimal b = Pub.ToDecimal(dr["净发货金额"].ToString());
                    //     decimal.TryParse(dr["退货金额"].ToString(), out decimal a);
                    //     decimal.TryParse(dr["净发货金额"].ToString(), out decimal b);
                    //     退货率 = a / b;
                    //     dataTableHZ.Rows[i]["退货率"] = 退货率;

                    // }
                    // else
                    // {
                    //     dataTableHZ.Rows[i]["退货率"] = 0.0;
                    // }
                    // if (decimal.TryParse(dr["往年净发货金额"].ToString(), out decimal result2) == true && result2 != 0)

                    // {
                    //     // decimal a = Math.Abs(Pub.ToDecimal(dr["往年退货金额"].ToString()));
                    //     // decimal b = Pub.ToDecimal(dr["往年净发货金额"].ToString());
                    //     decimal.TryParse(dr["往年退货金额"].ToString(), out decimal a);
                    //     decimal.TryParse(dr["往年净发货金额"].ToString(), out decimal b);
                    //     往年退货率 = Math.Round((a  / b), 2);
                    //     dataTableHZ.Rows[i]["往年退货率"] = 往年退货率;

                    // }
                    // else
                    // {
                    //     dataTableHZ.Rows[i]["往年退货率"] = 0.0;
                    // }

                

                }
            if (dataTableHZ.Columns.Contains("序号") == false)
            {
                dataTableHZ.Columns.Add("序号", typeof(int)).SetOrdinal(0);
            }
            
            for (int i = 0; i < dataTableHZ.Rows.Count; i++)
            {
                dataTableHZ.Rows[i]["序号"] = i + 1;
            }
            
            DataTableDynamicContext = new DataTableDynamicContext(dataTableHZ, (context, col) =>
             {
                 //产品编码
                 if (col.GetFieldName() == "产品编码" || col.GetFieldName() == "产品名称")
                 {
                     col.Fixed = true;
                     col.Width = 120;
                     col.TextEllipsis = true;
                 }
                 else if (col.GetFieldName() == "类别")
                 {
                     col.Width = 40;
                 }
                 else if (col.GetFieldName() == "序号")
                 {
                     col.Width = 40;
                 }
                 else if (col.GetFieldName() == "规格")
                 {
                     col.Width = 80;
                     col.TextEllipsis = true;
                 }
                 else if (col.GetFieldName() == "退货差异" || col.GetFieldName() == "发货数量" || col.GetFieldName() == "调入数量" || col.GetFieldName() == "调出数量" || col.GetFieldName() == "退货数量"
                 || col.GetFieldName() == "发货金额" || col.GetFieldName() == "调入金额" || col.GetFieldName() == "调出金额" || col.GetFieldName() == "退货金额")
                 {
                     col.Width = 65;
                 }
                 else if (col.GetFieldName() == "净发货差异" )
                 {
                     col.Width = 80;
                 }
                 else if ( col.GetFieldName() == "质量退货金额" || col.GetFieldName() == "质量退货数量" || col.GetFieldName() == "本期净发货数" || col.GetFieldName() == "同期净发货数")
                 {
                     col.Width = 90;
                 }
                 else if ( col.GetFieldName() == "本期净发货金额" || col.GetFieldName() == "同期净发货金额")
                 {
                     col.Width = 100;
                 }
                 else
                 {
                     col.Width = 100;
                 }
                 //所有列可排序
                 col.Sortable = true;
                 col.DefaultSort = true;
                 // col.TextWrap = true;

                 if (col.GetFieldName() == "退货率" || col.GetFieldName() == "往年退货率")
                 {
                     col.FormatString = "P2";
                     col.Width = 70;
                 }
             });
          
        }

        StateHasChanged();
    }
}
